package com.yyl.appx.dao

import androidx.fragment.app.Fragment
import androidx.lifecycle.LiveData
import androidx.lifecycle.ViewModel
import androidx.lifecycle.ViewModelProvider
import androidx.lifecycle.viewModelScope
import androidx.room.*
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.blankj.utilcode.util.Utils
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.launch
import java.util.*


const val WorkTableName = "work_table"
const val UserTableName = "user_table"


@Entity(
    tableName = UserTableName,
    foreignKeys = [
        ForeignKey(
            entity = Work::class,
            parentColumns = ["work_id"],
            childColumns = ["id_work"], // 最好有 index = true
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class User(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "user_id")
    var userId: Long,
    @ColumnInfo(name = "id_work", index = true)
    var idWork: Long,
    var name: String
)

@Entity(tableName = WorkTableName)
data class Work(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "work_id")
    var workId: Long,
    var nameWork: String,
    var price: Double
)

//@Entity 一对一
//data class OneToOnes(
//    @Embedded
//    var work: Work,
//    @Relation(parentColumn = "work_id", entityColumn = "id_work")
//    var user:User
//)
@Entity  //一对多
data class OneToMore(
    @Embedded
    var work: Work,
    @Relation(parentColumn = "work_id", entityColumn = "id_work")
    var userList: List<User>
)
//多对多
//不需要外键foreignKeys
//我们需要额外创建一个存储两个ID的 关联表 (也称为交叉引用表) 。
//两个ID为对应关系(共同持有对方)(A1=B1,A1=B2,A2=B2  A1有两个B,B1有一个A)
//查询时用SQL多表查询
@Entity(primaryKeys = ["work_id", "user_id"])
data class MoreToMore(
    var work_id: Long,
    var user_id: Long
)


@Entity
data class OtherRows(
    @Embedded
    var work: Work,
    @Relation(
        parentColumn = "work_id",
        entity = User::class,
        entityColumn = "user_id",
        projection = ["name"]
    )
    val userNames: List<String>
)

@Dao
interface DaoWorks {

    @Query("SELECT * FROM $WorkTableName")
    fun getAllWorks(): LiveData<List<Work>>

    @Query("SELECT * FROM $WorkTableName ORDER BY work_id DESC")
    fun findOrderByDesc(): LiveData<List<Work>>

    @Query("SELECT * FROM $WorkTableName ORDER BY work_id DESC LIMIT 1")
    suspend fun findOrderByDescFirst(): Work?

    @Query("SELECT * FROM $WorkTableName WHERE work_id BETWEEN :minId AND :maxId")
    suspend fun findMinMax(minId: Int, maxId: Int): Work?


    @Query("SELECT * FROM $WorkTableName WHERE nameWork LIKE :like OR nameWork LIKE :like2")
    suspend fun findLikeName(like: String, like2: String): List<Work>


    // 联表查询需要事物同步
    @Transaction
    @Query("SELECT * FROM $WorkTableName")
    fun getOneToMores(): LiveData<List<OneToMore>>

    // 联表查询需要事物同步
    @Transaction
    @Query("SELECT * FROM $WorkTableName where work_id=(:id)")
    suspend fun getOneToMore(id: Long): OneToMore?

    // 联表查询需要事物同步
    @Transaction
    @Query("SELECT * FROM $WorkTableName where work_id=(:id)")
    fun getOtherRow(id: Long): LiveData<OtherRows>

    // 不能主线程直接访问 ----只能子线程访问
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addWork(work: Work): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addWorks(vararg work: Work): LongArray

    @Update
    suspend fun updateWork(work: Work)

    @Delete
    suspend fun deleteWork(work: Work)

    @Query("delete from $WorkTableName where work_id in (:idList)")
    fun deleteWorks(idList: List<Int>)

    @Query("delete from $WorkTableName where work_id = :id")
    fun deleteWork(id: Int)

    //清空全部数据
    @Query("DELETE FROM $WorkTableName")
    fun clear()
}

@Dao
interface DaoUsers {
    @Query("SELECT * FROM $UserTableName")
    fun getAllUsers(): LiveData<List<User>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addUser(user: User): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addUsers(vararg work: Work): LongArray

    @Delete
    suspend fun deleteUser(user: User)

    @Update
    suspend fun updateUser(user: User)
}

//转换时间
@TypeConverters(DateConverter::class)
@Database(entities = [User::class, Work::class], version = 1, exportSchema = true)
abstract class DBTools : RoomDatabase() {
    abstract fun daoUsers(): DaoUsers
    abstract fun daoWorks(): DaoWorks

    companion object {
        val instance by lazy {
            Room.databaseBuilder(
                Utils.getApp(),
                DBTools::class.java,
                "app_database.db"
            )
//            所有表都会被丢弃，同时 新键 被插入
//                .fallbackToDestructiveMigration()
//                .addMigrations(MIGRATION_1_2)
                .build()
        }
    }
}

//SQLite的ALTER TABLE命令非常局限，只支持重命名表以及添加新的字段。
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        //此处对于数据库中的所有更新都需要写下面的代码
        database.execSQL(
            "ALTER TABLE $UserTableName ADD COLUMN last_update INTEGER"
        )
    }
}

// 实体类实现
class WorksViewModel : ViewModel() {

    val daoWork = DBTools.instance.daoWorks()
    val getWorks = daoWork.getAllWorks()
    val oneToMore = daoWork.getOneToMores()

    fun addWork(work: Work) {
        viewModelScope.launch(Dispatchers.IO) {
            daoWork.addWork(work)
        }
    }

    fun deleteWork(work: Work) {
        viewModelScope.launch(Dispatchers.IO) {
            daoWork.deleteWork(work)
        }
    }

    fun updateWork(work: Work) {
        viewModelScope.launch(Dispatchers.IO) {
            daoWork.updateWork(work)
        }
    }
}
// ViewMode 工厂
class ViewModelFactory() : ViewModelProvider.Factory {
    override fun <T : ViewModel> create(modelClass: Class<T>): T {
        if (modelClass.isAssignableFrom(WorksViewModel::class.java)) {
            return WorksViewModel() as T
        }
        return WorksViewModel() as T
    }
}
//使用方法
class ListWorksFragment : Fragment() {
    private val viewModel: WorksViewModel by lazy {
        ViewModelProvider(requireActivity(), ViewModelFactory())
            .get(WorksViewModel::class.java)
    }

    fun initView() {
        viewModel.getWorks.observe(viewLifecycleOwner) {
            // adapterListWorks.setWorks(it)
        }
    }

}

@Entity
data class Demo1(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "work_id")
    var workId: Long,
    var nameWork: Date,
    var price: Double
)

// TypeConverter 转换不能识别的类型
class DateConverter {

    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
}